/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package courseScheduler;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 *
 * @author david
 */
public final class Section {
    private int courseID;
    private int professorID;
    private int sectionID;
    private int id;

    Connection conn;

    PreparedStatement sectionGetStmnt;
    PreparedStatement sectionSetStmnt;
    PreparedStatement sectionInsertStmnt;

    Section (int newCourseID, int newProfessorID, int newSectionID){
        courseID = newCourseID;
        sectionID = newSectionID;
        professorID = newProfessorID;
    }

    /**
     * Constructor - Section
     * @param newConn
     * @param ID
     * @throws SQLException
     */
    Section (Connection newConn, int ID) throws SQLException
    {
        conn = newConn;
        conn.setAutoCommit(false);
        prepareGetStatement();
        sectionGetStmnt.setInt(1, ID);
        java.sql.ResultSet rs = sectionGetStmnt.executeQuery();
        if(rs.first()){
            int count = 0;
            do{
                count ++;
            } while (rs.next());
            rs.first();
            if (count == 1){
                courseID = rs.getInt(1);
                sectionID = rs.getInt(2);
                professorID = rs.getInt(3);
                id = ID;
            }
            else {
                javax.swing.JOptionPane.showMessageDialog(null, "Can not prcess; two entries found");
            }
        }
    }

    /**
     * Method to prepare select statement
     * @throws SQLException
     */
    private void prepareGetStatement() throws SQLException{
        sectionGetStmnt = conn.prepareStatement
        (
            "SELECT course_id,section_id,professor_id FROM section WHERE id =?"
        );
    }

    /**
     * Method to prepare update statement
     * @throws SQLException
     */
    private void prepareSetStatement() throws SQLException{
        sectionSetStmnt = conn.prepareStatement
        (
            "UPDATE section course_id = " + courseID + ",section_id = " + sectionID + ",professor_id = " + professorID + " WHERE id = ?;"
        );
    }

    private void prepareInsertStatement() throws SQLException {
        sectionInsertStmnt = conn.prepareStatement
        (
            "INSERT INTO section (section_id, course_id, professor_id) VALUES (" + sectionID + "," + courseID + "," + professorID + ")"
        );
    }
    /**
     * Method that updates and commits changes
     * @throws SQLException
     */
    public void setChanges() throws SQLException{
        prepareSetStatement();
        sectionSetStmnt.setInt(1, id);
        if (sectionSetStmnt.execute()){
            conn.commit();
        }
        else {
            conn.rollback();
        }
    }

    public static int getID(java.sql.Connection newConn, String sPrefix, int sNumber, int sSectionID) throws java.sql.SQLException{
        java.sql.Statement stmnt;
        java.sql.ResultSet rs;
        stmnt = newConn.createStatement();
        rs = stmnt.executeQuery("SELECT section.id FROM "
                + "section JOINS faculty ON section.professor_id = faculty.id "
                + "WHERE section.prefix LIKE \"" + sPrefix + "\" "
                + "AND course.number = " + sNumber + " AND section.section_id = " + sSectionID +";"
                );
        rs.first();
        return rs.getInt(1);
    }

    public static int[] getID(java.sql.Connection newConn, int id, String userType) throws java.sql.SQLException{
        java.sql.Statement stmnt;
        java.sql.ResultSet rs;
        stmnt = newConn.createStatement();
        rs = stmnt.executeQuery("SELECT section.course_id FROM "
                + "section WHERE section." + userType + "_id = " + id + ";"
                );
        rs.last();
        int returnInt [] = new int [rs.getRow()];
        rs.beforeFirst();
        int count = 0;
        while(rs.next()){
            returnInt [count] = rs.getInt(1);
        }
        return returnInt;
    }

    public final void setCourseID(int newCourseID){
        courseID = newCourseID;
    }

    public int getCourseID (){
        return courseID;
    }

    public final void setSectionID(int newSectionID){
        sectionID = newSectionID;
    }

    public int getSectionID(){
        return sectionID;
    }

    public final void setID(int newID){
        id = newID;
    }

    public int getID(){
        return id;
    }

    public final void setConnection(java.sql.Connection newConn){
        conn = newConn;
    }
}
